#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
将项目实施工作计划.md转换为Excel格式的脚本
"""

import pandas as pd
import re
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
from openpyxl.utils import get_column_letter
import os

def parse_resource_allocation_table(content):
    """
    解析资源分配表格
    """
    lines = content.split('\n')

    # 找到资源分配表格
    table_start = -1
    for i, line in enumerate(lines):
        if '| 阶段 | 需求分析师 |' in line:
            table_start = i
            break

    if table_start == -1:
        return None

    # 提取表格行
    table_lines = []
    for i in range(table_start, len(lines)):
        line = lines[i].strip()
        if line.startswith('|') and line.endswith('|'):
            table_lines.append(line)
        elif line == '' and len(table_lines) > 5:  # 遇到空行且已收集足够行时停止
            break

    # 解析表头
    header_line = table_lines[0]
    headers = [col.strip() for col in header_line.split('|')[1:-1]]

    # 解析数据行
    data = []
    for line in table_lines[1:]:
        if '|' in line:
            row_data = [col.strip() for col in line.split('|')[1:-1]]
            if len(row_data) == len(headers):
                # 处理最后一行总计（包含括号内容）
                if '**总计**' in row_data[0]:
                    # 清理数据中的**标记
                    row_data = [col.replace('**', '') for col in row_data]
                data.append(row_data)

    return headers, data

def parse_milestones(content):
    """
    解析里程碑信息
    """
    lines = content.split('\n')

    milestones = []
    for line in lines:
        # 匹配里程碑格式：- **M1: 需求冻结 (2025-10-09)**：更新用户需求说明、风险登记册。
        match = re.search(r'-\s*\*\*M(\d+):\s*([^()]+)\s*\(([^)]+)\)\*\*：(.+)', line)
        if match:
            milestone_num = match.group(1)
            name = match.group(2).strip()
            date = match.group(3).strip()
            deliverables = match.group(4).strip()
            milestones.append([milestone_num, name, date, deliverables])

    return milestones

def parse_gantt_tasks(content):
    """
    解析甘特图中的任务信息
    """
    lines = content.split('\n')

    # 找到甘特图开始
    gantt_start = -1
    for i, line in enumerate(lines):
        if '```mermaid' in line:
            gantt_start = i
            break

    if gantt_start == -1:
        return None

    tasks = []
    current_section = ""

    for i in range(gantt_start + 1, len(lines)):
        line = lines[i].strip()

        if line == '```':
            break

        # 跳过注释和格式行
        if line.startswith('gantt') or line.startswith('title') or line.startswith('dateFormat') or line.startswith('axisFormat') or line.startswith('todayMarker'):
            continue

        # 解析section
        if line.startswith('section'):
            current_section = line.replace('section', '').strip()
            continue

        # 解析任务行
        if ':' in line and not line.startswith('title'):
            # 示例：3.4 实时三维场景 : dev4, crit, after dev1, 2025-10-24, 40d
            if 'after' in line:
                # 有依赖关系的任务
                parts = line.split(':')
                task_info = parts[0].strip()
                task_details = parts[1].strip()

                # 解析任务详情
                details_parts = [p.strip() for p in task_details.split(',')]

                task_id = ""
                start_date = ""
                duration = ""
                dependency = ""
                critical = False

                for part in details_parts:
                    if part.startswith('after'):
                        dependency = part.replace('after', '').strip()
                    elif part == 'crit':
                        critical = True
                    elif 'd' in part:
                        duration = part
                    elif '-' in part and len(part) == 10:  # 日期格式
                        start_date = part
                    else:
                        task_id = part

                tasks.append({
                    'section': current_section,
                    'task': task_info,
                    'task_id': task_id,
                    'start_date': start_date,
                    'duration': duration,
                    'dependency': dependency,
                    'critical': critical
                })
            else:
                # 无依赖关系的任务
                parts = line.split(':')
                if len(parts) >= 2:
                    task_info = parts[0].strip()
                    task_details = parts[1].strip()

                    details_parts = [p.strip() for p in task_details.split(',')]
                    start_date = details_parts[0] if len(details_parts) > 0 else ""
                    duration = details_parts[1] if len(details_parts) > 1 else ""

                    tasks.append({
                        'section': current_section,
                        'task': task_info,
                        'task_id': "",
                        'start_date': start_date,
                        'duration': duration,
                        'dependency': "",
                        'critical': False
                    })

    return tasks

def format_resource_sheet(ws, headers, data):
    """
    格式化资源分配工作表
    """
    # 设置列宽
    column_widths = {
        '阶段': 15,
        '需求分析师': 12,
        'HMI设计师': 12,
        '技术架构师': 12,
        '前端开发': 12,
        '后端开发': 12,
        '技术艺术家': 12,
        '测试工程师': 12,
        'DevOps': 10,
        '阶段总人天': 12
    }

    for col_num, header in enumerate(headers, 1):
        col_letter = get_column_letter(col_num)
        width = column_widths.get(header, 15)
        ws.column_dimensions[col_letter].width = width

    # 设置样式
    header_font = Font(name='微软雅黑', size=10, bold=True, color='FFFFFF')
    header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
    summary_font = Font(name='微软雅黑', size=10, bold=True, color='FF0000')
    summary_fill = PatternFill(start_color='FFE6E6', end_color='FFE6E6', fill_type='solid')
    data_font = Font(name='微软雅黑', size=9)
    border = Border(
        left=Side(style='thin', color='000000'),
        right=Side(style='thin', color='000000'),
        top=Side(style='thin', color='000000'),
        bottom=Side(style='thin', color='000000')
    )

    # 添加表头
    for col_num, header in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col_num, value=header)
        cell.font = header_font
        cell.fill = header_fill
        cell.border = border
        cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)

    # 添加数据行
    for row_num, row_data in enumerate(data, 2):
        is_total_row = '总计' in row_data[0]

        for col_num, value in enumerate(row_data, 1):
            cell = ws.cell(row=row_num, column=col_num, value=value)
            cell.border = border

            if is_total_row:
                cell.font = summary_font
                cell.fill = summary_fill
            else:
                cell.font = data_font

            # 数值列居中对齐
            if col_num > 1:  # 除了第一列外的所有列
                if str(value).isdigit():
                    cell.value = int(value)
                cell.alignment = Alignment(horizontal='center', vertical='center')
            else:
                cell.alignment = Alignment(horizontal='left', vertical='center')

def format_milestones_sheet(ws, milestones):
    """
    格式化里程碑工作表
    """
    # 设置列宽
    ws.column_dimensions['A'].width = 10
    ws.column_dimensions['B'].width = 25
    ws.column_dimensions['C'].width = 15
    ws.column_dimensions['D'].width = 40

    # 设置样式
    header_font = Font(name='微软雅黑', size=10, bold=True, color='FFFFFF')
    header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
    data_font = Font(name='微软雅黑', size=9)
    border = Border(
        left=Side(style='thin', color='000000'),
        right=Side(style='thin', color='000000'),
        top=Side(style='thin', color='000000'),
        bottom=Side(style='thin', color='000000')
    )

    # 添加表头
    headers = ['里程碑', '名称', '日期', '交付物']
    for col_num, header in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col_num, value=header)
        cell.font = header_font
        cell.fill = header_fill
        cell.border = border
        cell.alignment = Alignment(horizontal='center', vertical='center')

    # 添加里程碑数据
    for row_num, milestone in enumerate(milestones, 2):
        for col_num, value in enumerate(milestone, 1):
            cell = ws.cell(row=row_num, column=col_num, value=value)
            cell.font = data_font
            cell.border = border

            if col_num == 1:  # 里程碑编号
                cell.alignment = Alignment(horizontal='center', vertical='center')
            elif col_num == 3:  # 日期
                cell.alignment = Alignment(horizontal='center', vertical='center')
            else:
                cell.alignment = Alignment(horizontal='left', vertical='center', wrap_text=True)

def format_gantt_sheet(ws, tasks):
    """
    格式化甘特图任务工作表
    """
    # 设置列宽
    ws.column_dimensions['A'].width = 20
    ws.column_dimensions['B'].width = 25
    ws.column_dimensions['C'].width = 10
    ws.column_dimensions['D'].width = 12
    ws.column_dimensions['E'].width = 12
    ws.column_dimensions['F'].width = 15
    ws.column_dimensions['G'].width = 10

    # 设置样式
    header_font = Font(name='微软雅黑', size=10, bold=True, color='FFFFFF')
    header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
    critical_font = Font(name='微软雅黑', size=9, color='FF0000', bold=True)
    data_font = Font(name='微软雅黑', size=9)
    border = Border(
        left=Side(style='thin', color='000000'),
        right=Side(style='thin', color='000000'),
        top=Side(style='thin', color='000000'),
        bottom=Side(style='thin', color='000000')
    )

    # 添加表头
    headers = ['阶段', '任务', '任务ID', '开始日期', '持续时间', '依赖', '关键任务']
    for col_num, header in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col_num, value=header)
        cell.font = header_font
        cell.fill = header_fill
        cell.border = border
        cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)

    # 添加任务数据
    for row_num, task in enumerate(tasks, 2):
        ws.cell(row=row_num, column=1, value=task['section']).font = data_font
        ws.cell(row=row_num, column=2, value=task['task']).font = data_font
        ws.cell(row=row_num, column=3, value=task['task_id']).font = data_font
        ws.cell(row=row_num, column=4, value=task['start_date']).font = data_font
        ws.cell(row=row_num, column=5, value=task['duration']).font = data_font
        ws.cell(row=row_num, column=6, value=task['dependency']).font = data_font

        # 关键任务列
        critical_cell = ws.cell(row=row_num, column=7, value='是' if task['critical'] else '否')
        if task['critical']:
            critical_cell.font = critical_font
        else:
            critical_cell.font = data_font

        # 设置边框和对齐
        for col_num in range(1, 8):
            cell = ws.cell(row=row_num, column=col_num)
            cell.border = border

            if col_num in [4, 5, 7]:  # 日期、持续时间、关键任务列居中
                cell.alignment = Alignment(horizontal='center', vertical='center')
            else:
                cell.alignment = Alignment(horizontal='left', vertical='center', wrap_text=True)

def main():
    """
    主函数：读取项目实施工作计划并转换为Excel
    """
    md_file = '/Users/xueyuheng/workspace/ubbc/ubbc5/项目实施工作计划.md'
    excel_file = '/Users/xueyuheng/workspace/ubbc/ubbc5/项目实施工作计划.xlsx'

    # 检查文件是否存在
    if not os.path.exists(md_file):
        print(f"错误：找不到文件 {md_file}")
        return

    try:
        # 读取markdown文件
        with open(md_file, 'r', encoding='utf-8') as f:
            content = f.read()

        # 创建Excel工作簿
        wb = Workbook()

        # 删除默认工作表
        wb.remove(wb.active)

        # 解析并创建资源分配工作表
        resource_headers, resource_data = parse_resource_allocation_table(content)
        if resource_headers and resource_data:
            ws_resource = wb.create_sheet('资源分配')
            format_resource_sheet(ws_resource, resource_headers, resource_data)
            print("✅ 已创建资源分配工作表")

        # 解析并创建里程碑工作表
        milestones = parse_milestones(content)
        if milestones:
            ws_milestones = wb.create_sheet('里程碑')
            format_milestones_sheet(ws_milestones, milestones)
            print("✅ 已创建里程碑工作表")

        # 解析并创建甘特图任务工作表
        gantt_tasks = parse_gantt_tasks(content)
        if gantt_tasks:
            ws_gantt = wb.create_sheet('甘特图任务')
            format_gantt_sheet(ws_gantt, gantt_tasks)
            print("✅ 已创建甘特图任务工作表")

        # 保存Excel文件
        wb.save(excel_file)
        print(f"\n✅ 成功将 {md_file} 转换为 {excel_file}")
        print(f"创建了 {len(wb.sheetnames)} 个工作表：{', '.join(wb.sheetnames)}")

    except Exception as e:
        print(f"❌ 转换过程中出现错误：{e}")

if __name__ == '__main__':
    main()
